using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Framework.Library.Components.Providers;
using Framework.Library.Utilities.Database;
using System.Data.SqlClient;

namespace Framework.Library.Components.Database
{    
    /// <summary>
    /// Views manage class
    /// </summary>
    public class _View : Info
    {
        #region "variables"
        private SqlDataReader _Definition;
        #endregion

        #region "Properties"
        /// <summary>
        /// View Definition
        /// </summary>
        public SqlDataReader Definition
        {
            get
            {
                return _Definition;
            }
        }
        #endregion

        /// <summary>
        /// Constructor
        /// </summary>
        public _View()
        {

        }
        /// <summary>
        /// Get All Views
        /// </summary>
        public DataTable GetViews()
        {
            DataTable dtCommands = DataAccessProvider.Instance().GetViews();
            return dtCommands;
        }
        /// <summary>
        /// Gets the view columns
        /// </summary>
        /// <returns></returns>
        public DataTable GetColumns()
        {
            StringBuilder _sqlCommand = new StringBuilder();
            _sqlCommand.Append("SELECT DISTINCT dbo.syscolumns.name, dbo.systypes.name AS name_t, dbo.systypes.type, dbo.syscolumns.length, dbo.syscolumns.prec, ");
            _sqlCommand.Append(" dbo.syscolumns.scale, dbo.syscolumns.colid, dbo.syscolumns.usertype, dbo.syscolumns.isnullable, dbo.syscolumns.status, dbo.syscolumns.iscomputed");
            _sqlCommand.Append(" FROM dbo.syscolumns LEFT OUTER JOIN dbo.systypes ON dbo.syscolumns.xusertype = dbo.systypes.xusertype");
            _sqlCommand.Append(" WHERE (dbo.syscolumns.id = OBJECT_ID('" + this.GetObjectName().Replace("'", "''") + "'))");
            _sqlCommand.Append(" ORDER BY dbo.syscolumns.colid");

            return DataAccessProvider.Instance().ReturnDataSet(_sqlCommand.ToString());
        }
        /// <summary>
        /// Gets the view definition
        /// </summary>
        public void SetDefinition()
        {
            StringBuilder _sqlCommand = new StringBuilder();
            _sqlCommand.Append("EXEC sp_helptext '" + this.GetObjectName().Replace("'", "''") + "'");
            _Definition = DataAccessProvider.Instance().ReturnReader(_sqlCommand.ToString());
        }
        /// <summary>
        /// Gest the view reference
        /// </summary>
        /// <returns></returns>
        public DataTable GetReferences()
        {
            StringBuilder _sqlCommand = new StringBuilder();
            _sqlCommand.Append("SELECT DISTINCT dbo.sysobjects.id as id, USER_NAME(dbo.sysobjects.uid) +'.'+ dbo.sysobjects.name as completeName, dbo.sysobjects.name, dbo.sysobjects.sysstat & 7 AS stat, dbo.sysobjects.xtype, 'Refd' as type");
            _sqlCommand.Append(" FROM dbo.sysobjects INNER JOIN dbo.sysdepends ON dbo.sysobjects.id = dbo.sysdepends.depid");
            _sqlCommand.Append(" WHERE (dbo.sysdepends.id = OBJECT_ID('" + this.GetObjectName().Replace("'", "''") + "'))");
            _sqlCommand.Append(" UNION");
            _sqlCommand.Append(" SELECT DISTINCT O.id as id, USER_NAME(O.uid) +'.'+ O.name as completeName, USER_NAME(O.uid) +'.'+ O.name, O.sysstat & 7,O.xtype, 'Ref' as type");
            _sqlCommand.Append(" FROM sysobjects O,sysdepends D ");
            _sqlCommand.Append(" WHERE D.depid=OBJECT_ID('" + this.GetObjectName().Replace("'", "''") + "') ");
            _sqlCommand.Append(" AND D.id=O.id AND O.sysstat & 7  != 0 ");
            _sqlCommand.Append(" AND type <> 'C' ");
            _sqlCommand.Append(" UNION SELECT DISTINCT O.id as id, USER_NAME(O.uid)+'.'+OBJECT_NAME(O.parent_obj) + '.' +O.name as completeName, USER_NAME(O.uid)+'.'+OBJECT_NAME(O.parent_obj) + '.' +O.name,O.sysstat & 7,O.xtype, 'Ref' as type");
            _sqlCommand.Append(" FROM sysobjects O,sysdepends D ");
            _sqlCommand.Append(" WHERE D.depid=OBJECT_ID('" + this.GetObjectName().Replace("'", "''") + "') ");
            _sqlCommand.Append(" AND D.id=O.id AND O.sysstat & 7 = 0");

            return DataAccessProvider.Instance().ReturnDataSet(_sqlCommand.ToString());
        }
        /// <summary>
        /// Renames the selected view with a default name
        /// </summary>
        public void Rename()
        {
            string _sqlCommand = "EXEC sp_rename '" + this.GetObjectName().Replace("'", "''") + "', '" + (this.Name + DateTime.UtcNow.Ticks.ToString()).Replace("'", "''") + "'";
            DataAccessProvider.Instance().ExecuteCommand(_sqlCommand);
        }
        /// <summary>
        /// Load view properties, afert setting the object ID
        /// </summary>
        public void LoadProperties()
        {
            string _sqlCommand = "SELECT name, USER_NAME(uid) FROM sysobjects WHERE id = " + ID.ToString();
            SqlDataReader myReader = DataAccessProvider.Instance().ReturnReader(_sqlCommand);
            while (myReader.Read())
            {
                this.Name = myReader.GetString(0);
                this.Owner = myReader.GetString(1);
            }
        }
        /// <summary>
        /// Drops the selected view
        /// </summary>
        public void Drop()
        {
            string _sqlCommand = "DROP VIEW " + this.GetObjectName() + "";
            DataAccessProvider.Instance().ExecuteCommand(_sqlCommand);
        }
        /// <summary>
        /// Create view
        /// </summary>
        public void Create(string sqlCommand)
        {
            DataAccessProvider.Instance().ExecuteCommand(sqlCommand);
        }        
        /// <summary>
        /// Execute the querry
        /// </summary>
        public void ExecuteQuery(string _sqlCommand)
        {
            DataAccessProvider.Instance().ExecuteCommand(_sqlCommand);
        }

    }
}
